﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Plugin
{
    public class Datenbankmanager
    {
        private string sCxn = "server=MARCO-TIMELINE\\SQLEXPRESS;User ID=swe_user;Password=swe123;Initial Catalog=swe_database";

        public Datenbankmanager()
        {
        }
        public string insertNewEntry(string url)
        {
            if (string.IsNullOrWhiteSpace(url))
                return null;

            SqlConnection myConnection = null;
            try
            {
                string randomKey = randomString(5);

                myConnection = new SqlConnection(sCxn);
                myConnection.Open();
                SqlCommand myCommand = new SqlCommand("update urlTable SET url=@url WHERE id=@id", myConnection);
                myCommand.Parameters.AddWithValue("@id", randomKey);
                myCommand.Parameters.AddWithValue("@url", url);

                int rows = myCommand.ExecuteNonQuery();
                if (rows > 0)
                {
                    return randomKey;
                }
                else
                {
                    myCommand = new SqlCommand("insert into urlTable (id, url) values(@id, @url);", myConnection);
                    myCommand.Parameters.AddWithValue("@id", randomKey);
                    myCommand.Parameters.AddWithValue("@url", url);

                    rows = myCommand.ExecuteNonQuery();
                    if (rows > 0)
                        return randomKey;
                    else
                        return null;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: Url-Plugin\nOccurred in Datenbankmanager: " + e.Message + "\n" + e.StackTrace);
                return null;
            }
            finally
            {
                if (myConnection != null)
                    myConnection.Close();
            }
        }
        public string getUrlByKey(string key)
        {
            if (string.IsNullOrWhiteSpace(key))
                return null;

            SqlConnection myConnection = null;
            try
            {
                myConnection = new SqlConnection(sCxn);
                myConnection.Open();
                SqlCommand myCommand = new SqlCommand("select url from urlTable where id=@id", myConnection);
                myCommand.Parameters.AddWithValue("@id", key);

                string result = (string)myCommand.ExecuteScalar();
                if (string.IsNullOrEmpty(result))
                {
                    return null;
                }
                else
                {
                    return result;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: Url-Plugin\nOccurred in Datenbankmanager: " + e.Message + "\n" + e.StackTrace);
                return null;
            }
            finally
            {
                if (myConnection != null)
                    myConnection.Close();
            }
        }
        public string randomString(int length)
        {
            string ret = string.Empty;
            System.Text.StringBuilder SB = new System.Text.StringBuilder();
            string Content = "1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz";
            Random rnd = new Random();
            for (int i = 0; i < length; i++)
                SB.Append(Content[rnd.Next(Content.Length)]);
            return SB.ToString();
        }
    }
}
